﻿Module GenericDao

    Public Sub insert(ByRef obj As GenericObject)
        DataAccessConnection.openConnection()

        Dim command As New OracleClient.OracleCommand

        Dim query As String = ""
        query = "INSERT INTO " & obj.getTableName & " (name, description) "
        query &= "values('" & obj.nameProperty & "', '" & obj.descriptionProperty & "')"

        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = query

        command.ExecuteNonQuery()

        DataAccessConnection.closeConnection()
    End Sub

    Public Sub update(ByRef obj As GenericObject)
        DataAccessConnection.openConnection()
        Dim command As New OracleClient.OracleCommand

        Dim query As String = ""

        query = "UPDATE " & obj.getTableName & " "
        query &= " SET name = '" & obj.nameProperty & "' , "
        query &= " description = '" & obj.descriptionProperty & "' "
        query &= " WHERE id = " & obj.idProperty & ""

        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = query

        command.ExecuteNonQuery()

        DataAccessConnection.closeConnection()

    End Sub

    Public Sub deleteById(ByRef obj As IObject)
        DataAccessConnection.openConnection()
        Dim command As New OracleClient.OracleCommand
        Dim table As New Data.DataTable

        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "DELETE FROM " & obj.getTableName & " WHERE id = " & obj.getId

        'table.Load(command.ExecuteReader)
        DataAccessConnection.closeConnection()
    End Sub

    Public Function searchData(ByRef obj As IObject, ByVal stringSelect As String) As Data.DataTable
        DataAccessConnection.openConnection()

        Dim command As New OracleClient.OracleCommand
        Dim table As New Data.DataTable

        command.CommandType = CommandType.Text
        command.CommandText = "SELECT " & stringSelect & " FROM " & obj.getTableName

        command.Connection = connection

        table.Load(command.ExecuteReader)

        DataAccessConnection.closeConnection()

        Return table
    End Function


    Public Function searchDataByFilter(ByRef obj As GenericObject)
        DataAccessConnection.openConnection()

        Dim command As New OracleClient.OracleCommand
        Dim table As New Data.DataTable

        Dim query As String = ""

        query = "SELECT * FROM " & obj.getTableName & " WHERE "
        query &= " name like '%" & obj.nameProperty & "%'"
        query &= " AND (id = " & obj.idProperty & " OR " & obj.idProperty & " = 0)"

        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = query

        table.Load(command.ExecuteReader)

        DataAccessConnection.closeConnection()

        Return table
    End Function

    ' JFabro. 10/10/2011
    Public Function searchDataById(ByRef obj As GenericObject) As GenericObject
        DataAccessConnection.openConnection()

        Dim command As New OracleClient.OracleCommand
        Dim query As String
        Dim reader As OracleClient.OracleDataReader
        query = "SELECT * FROM " & obj.getTableName
        query &= " WHERE id =" & obj.idProperty

        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = query

        reader = command.ExecuteReader
        reader.Read()
        obj.nameProperty = reader("name")
        obj.descriptionProperty = IIf(IsDBNull(reader("description")), "", reader("description"))

        DataAccessConnection.closeConnection()
        Return obj
    End Function

End Module
